Node / ReticulumProjects / MeshChatX.git / files / docs / agents / skills / database-migrations-backups / SKILL.md

Displaying Rendered • View raw • Download

Bump schema versions correctly, keep backups and snapshots safe, and never conflate identity-key restore with full database zip restore.

• Changing SQLite schema or migrations

• Touching backup, snapshot, restore, or crash-recovery paths

• Adding tables / columns used by conversation or settings features

• Engine is SQLite with explicit SQL. No ORM.

• Bump T383838LATEST_VERSION in T383838meshchatx/src/backend/database/schema.py and add a migration path.

• Test upgrade from an older version when the change is non-trivial.

• Heavy data backfills in migrations should skip when the target table is empty. Fresh T383838Database Initialization benches create empty DBs and still run every migration step.

• Prefer T383838CREATE TABLE, T383838ADD COLUMN with NULL or default, and indexes.

• Avoid T383838DROP TABLE, T383838DROP COLUMN, table renames, and destructive T383838DELETE FROM in the same release that new app code depends on.

• Defer drops and renames to N+2 (two-release lag).

• When T383838LATEST_VERSION bumps, note the schema change in T383838CHANGELOG.md.

• CI runs T383838scripts/ci/check_schema_migrations.py on T383838schema.py changes. Rare destructive steps need T383838# migration-safety: allow-destructive on the same line.

• Backups skip T383838database-backups/ and T383838snapshots/ so a new zip does not nest itself (T383838BACKUP_SKIP_DIR_NAMES).

• Suspicious shrink writes T383838backup-SUSPICIOUS-*.zip and skips rotation. Do not treat that as a normal backup.

• Checkpoint WAL before zip snapshots when the live DB is open.

• Before applying schema upgrades (T383838current_version below T383838LATEST_VERSION), write T383838backup-pre-migrate-v*-to-v*.zip under T383838database-backups/ unless T383838MESHCHAT_SKIP_PRE_MIGRATE_BACKUP=1. Migration aborts if that backup fails.

• After migrate, T383838PRAGMA quick_check and T383838SELECT 1 must pass before the version row is updated. Failures log T383838schema_migration ... status=failed and block startup.

• Prune older T383838backup-pre-migrate-*.zip files, keeping five by default (T383838MESHCHAT_PRE_MIGRATE_BACKUP_KEEP, T3838380 disables pruning).

• T383838database_version greater than T383838LATEST_VERSION raises T383838DatabaseTooNewError at startup.

• Worker-thread connections must share T383838DatabaseProvider pragmas (see T383838landlock-sqlite).

• One storage directory per running instance: T383838StorageLock serializes migration and runtime (do not run two replicas on one T383838/config volume).

When T383838LATEST_VERSION changes:

T282828

This writes T383838tests/backend/fixtures/schema_versions/schema_v{N}.db for latest, N-1, and N-2 plus T383838manifest.json (manifest is committed, T383838*.db files are gitignored and generated before backend tests).

Details for pickers and tutorial copy: T383838identity-restore.

• T383838meshchatx/src/backend/database/schema.py

• T383838meshchatx/src/backend/database/__init__.py

• T383838meshchatx/meshchat.py (backup / restore routes, T383838prepare_for_database_restore)

• T383838electron/offlineRecovery.js

• T383838scripts/ci/check_schema_migrations.py

• T383838scripts/ci/schema_fixture_generate.py

T282828

uv run pytest tests/backend/test_database_snapshots.py tests/backend/test_schema_migration_upgrade.py tests/backend/test_schema_migration_matrix.py -q --tbTff7b72=short